<?php
class Banking_balance extends Admin_Controller
{
	public function __construct()
	{
		parent::__construct();
		$this->load->model('voucher/fund_model', 'fund');
		$this->load->model('registration/register_model','register');
		$this->load->model('registration/register_detail_model','register_detail');
		$this->load->model('registration/register_title_model', 'register_title');
		$this->load->model('balance_model', 'balance');
		$this->load->model('balance_detail_model', 'balance_detail');
		$this->load->model('balance_check_model', 'balance_check');
	}
	
	public function form($fund_id, $year, $month, $id = FALSE)
	{
		$data['fund'] = $this->fund->get_row($fund_id);
		$data['year'] = $year;
		$data['month'] = $month;
		$agency_id = login_data('agency_id');
		$data['agency_id'] = $agency_id;
		$data['user_id'] = login_data('id');

		$data['register_id'] = $this->db->getone(
			'SELECT ID
			FROM REGISTERS
			WHERE FUND_ID = ?
			AND AGENCY_ID = ?
			AND YEAR(DATE_RECORD) = ?
			AND MONTH(DATE_RECORD) = ?
			AND TITLE_ID = 43'
		,array($fund_id, $agency_id, ($year - 543), $month));
		$data['rs'] = $this->balance->get_by($fund_id, $agency_id, $year, $month);
		$this->template->build('form', $data);
	}
	
	public function save()
	{
		if($_POST)
		{		
			Fund::delete_all($_POST['fund_id'], $_POST['agency_id']);
			
			//$this->balance->delete_by($_POST['fund_id'], $_POST['agency_id'], $_POST['year_record'], $_POST['month_record']);
			$data = array(
				'fund_id'  => $_POST['fund_id'],
				'agency_id' => login_data('agency_id'),
				'year_record' => ($_POST['year_record']-543),
				'month_record' => $_POST['month_record'],
				'user_id' => login_data('id')
			);
			$balance_id = $this->balance->save($data);
			$total = 0;
			foreach($_POST['price'] as $title_id => $price)
			{
				$detail = array(
					'balance_id' => $balance_id,
					'title_id' => $title_id,
					'price' => (!empty($price)) ? str_ireplace(',', '', $price) : 0
				);
				$this->balance_detail->save($detail);
			}	
			// บันทึกยอดยกไปลงทะเบียนคุมเงิน
			$this->save_next_balance($_POST['fund_id'], $_POST['agency_id'], $_POST['user_id'], $_POST['year_record'], $_POST['month_record'], $_POST['total'], $_POST['register_id']);
			
			$_POST['month_record'] += 1;
			if($_POST['month_record'] == 13){ $_POST['year_record'] += 1; $_POST['month_record'] = 1;  }
			
			$this->load->model('voucher/status_model');
			$status = array(
				'fund_id' => $_POST['fund_id'],
				'agency_id' => $_POST['agency_id'],
				'user_id' => $_POST['user_id'],
				'status_year' => ($_POST['year_record']-543),
				'status_month' => $_POST['month_record'],
				'status' => 0
			);
			//$this->db->execute('DELETE FROM STATUS WHERE FUND_ID = ? AND AGENCY_ID = ? AND STATUS_YEAR = ? AND STATUS_MONTH = ?',
			//array($_POST['fund_id'], $_POST['agency_id'], $_POST['year_record'], $_POST['month_record']));
			$this->status_model->save($status);
			add_log('บันทึกยอดยกมาครั้งแรก '.Fund::fund_name($_POST['fund_id']).' หน่วยงาน '.Fund::agency_name($_POST['agency_id']).' ปี '.$_POST['year_record'].' เดือน '.month($_POST['month_record']));
		}
		//redirect('registration/index/'.$_POST['fund_id'].'/'.$_POST['year_record'].'/'.$_POST['month_record']);
		redirect('registration/index/'.$_POST['fund_id'].'/'.Fund::lastest($_POST['fund_id'], $_POST['agency_id']));
	}
	
	public function view($fund_id, $year, $month, $agency_id = NULL)
	{	
		$this->check_fisrt_start();
		$agency_id = (empty($agency_id)) ? login_data('agency_id') : $agency_id;
		// ค้นหา
		if($_GET)
		{
			$agency_id = (empty($_GET['agency_id'])) ? NULL : '/'.$_GET['agency_id'];
			redirect('banking_balance/view/'.$fund_id.'/'.$_GET['year'].'/'.$_GET['month'].$agency_id);
			exit;
		}

		$data['fund'] = $this->fund->get_row($fund_id);
		$data['year'] = $year;
		$data['month'] = $month;
		$data['agency_id'] = $agency_id;
		
		$year = Fund::convert_to_real_year($year, $month);
		
		// ยอดคงเหลือตามรายงานรับ-จ่ายเงินประจำเดือน
		
		$data['income_expenses_balance'] = $this->income_expenses_balance($fund_id, $agency_id, $year, $month);
		
		// ตรวจสอบ ว่าเคยบันทึกงบพิสูจน์ยอดเงินฝากธนาคารหรือยัง
		$year_record = $year - 543;
		$sql = 'SELECT ID, BALANCE_PRICE
		FROM BALANCES
		WHERE FUND_ID = ?
		AND AGENCY_ID = ?
		AND YEAR_RECORD = ?
		AND MONTH_RECORD = ?';
		//$this->db->debug = true;
		$balance_id = $this->db->getone($sql,array($fund_id, $agency_id, ($data['year'] - 543), $month));
		//$this->db->debug = FALSE;
		$data['balance'] = NULL;
		$data['balance_checks'] = NULL;
		
		$data['register_id'] = NULL;
		
		if(!empty($balance_id))
		{
			
			$data['register_id'] = $this->get_next_balance($fund_id, $agency_id, $year, $month);
			//$this->db->debug = TRUE;
			$data['balance'] = $this->balance->get_row($balance_id);
			//$this->db->debug = FALSE;
			// รายละเอียดเช็คที่สั่งจ่ายแล้วแต่ยังไม่นำไปขึ้นเงิน
			$data['balance_checks'] = $this->balance_check->where('balance_id = '.$balance_id)->order_by('date_record asc')->get(FALSE, FALSE); 
			
			/*
			// รายละเอียดเงินคงเหลือตามรายงานรับ - จ่ายเงินประจำเดือน 
			$sql = 'SELECT REGISTER_TITLES.COL_TITLE AS TITLE, BALANCE_DETAILS.TITLE_ID, BALANCE_DETAILS.PRICE AS VALUE 
			FROM BALANCE_DETAILS
			JOIN REGISTER_TITLES ON REGISTER_TITLES.ID = BALANCE_DETAILS.TITLE_ID
			WHERE BALANCE_ID = 4
			ORDER BY BALANCE_DETAILS.ID ASC';
			$data['result'] = $this->db->getarray($sql, $balance_id);
			dbConvert($data['result']);
			*/
		}
		//else
		//{
			/*
			 * รายละเอียดเงินคงเหลือตามรายงานรับ - จ่ายเงินประจำเดือน
			 */
			$titles = $this->fix_title();
			$bl = $this->balance->get_by($fund_id, $agency_id, $year, $month, TRUE);
			$cur = $this->month_price($fund_id, $agency_id, $year, $month);
			
			dbConvert($titles);
			$data['result'] = array(
				
				/*
				 * ค่าใช้จ่ายสงเคราะห์รายบุคคล
				 */
				0 => array(
					'title_id' => 14,
					'title' => $titles[14],
					'value' => $bl[14] + $cur[1] + $cur[14] - $cur[28] - $cur[35]
				),
				
				/*
				 * ค่าใช้จ่ายสนับสนุนโครงการ
				 */
				1 => array(
					'title_id' => 15,
					'title' => $titles[15],
					'value' => $bl[15] + $cur[2] + $cur[15] - $cur[29] - $cur[36]
				),
				
				/*
				 * ค่าใช้จ่ายในการดำเนินงานของผู้คุ้มครองสวัสดิภาพ
				 */
				2 => array(
					'title_id' => 16,
					'title' => $titles[16],
					'value' => $bl[16] + $cur[3] + $cur[14] - $cur[30] - $cur[37]
				),
				
				/*
				 * ค่าเบี้ยประชุม
				 */
				3 => array(
					'title_id' => 17,
					'title' => $titles[17],
					'value' => $bl[17] + $cur[4] + $cur[15] - $cur[31] - $cur[38]
				),
				
				/*
				 * ค่าใช้จ่ายในการประชุม
				 */
				4 => array(
					'title_id' => 18,
					'title' => $titles[18],
					'value' => $bl[18] + (($cur[5] + $cur[16]) - ($cur[32] + $cur[39]))
				),
				
				/*
				 * ค่าใช้จ่ายอื่น
				 */
				5 => array(
					'title_id' => 19,
					'title' => $titles[19],
					'value' => $bl[19] + (($cur[6] + $cur[17]) - ($cur[33] + $cur[40]))
				),
				
				/*
				 * ค่าธรรมเนียมธนาคาร
				 */
				6 => array(
					'title_id' => 20,
					'title' => $titles[20],
					'value' => $bl[20] + (($cur[7] + $cur[18]) - ($cur[34] + $cur[41]))
				),
				
				/*
				 * ลูกหนี้เงินยืมกองทุนคุ้มครองเด็ก
				 */
				7 => array(
					'title_id' => 21,
					'title' => $titles[21],
					'value' => $bl[21] + $cur[21] - $cur[42]
				),
				
				/*
				 * รายได้ดอกเบี้ย
				 */
				8 => array(
					'title_id' => 8,
					'title' => $titles[8],
					'value' => $bl[8] + $cur[8] - $cur[22]
				),
				
				/*
				 * รายได้จากการรับบริจาค
				 */
				9 => array(
					'title_id' => 9,
					'title' => $titles[9],
					'value' => $bl[9] + $cur[9] - $cur[23]
				),
				
				/*
				 * รายได้ค่าปรับ
				 */
				10 => array(
					'title_id' => 10,
					'title' => $titles[10],
					'value' => $bl[10] + $cur[10] - $cur[24]
				),
				
				/*
				 * รายได้อื่น
				 */
				11 => array(
					'title_id' => 11,
					'title' => $titles[11],
					'value' => $bl[11] + $cur[11] - $cur[25]
				),
				
				/*
				 * รายได้รับคืนเงินสงเคราะห์รายบุคคล
				 */
				12 => array(
					'title_id' => 12,
					'title' => $titles[12],
					'value' => $bl[12] + $cur[12] - $cur[26]
				),
				
				/*
				 * รายได้รับคืนเงินสนับสนุนโครงการ
				 */
				13 => array(
					'title_id' => 13,
					'title' => $titles[13],
					'value' => $bl[13] + $cur[13] - $cur[27]
				)
			);
		//}
				
		
		
		
		$this->template->append_metadata(js_datepicker());
		$this->template->append_metadata(js_print());
		$this->template->build('view', $data);
	}
	
	/*
	 * เก็บข้อมูลประจำเดือน
	 */
	public function save_balance()
	{
		//echo '<meta http-equiv="Content-Type" content="text/html; charset=utf-8">';
		if($_POST)
		{	//$this->db->debug = true;
			
			if(empty($_POST['id']))
			{
				add_log('บันทึกงบพิสูจน์ยอดเงินฝากธนาคาร กองทุน  "'.$this->fund->get_one('name',$_POST['fund_id']).'" ปี '.$_POST['year'].' เดือน '.month($_POST['month']));
			}
			else
			{
				add_log('แก้ไขงบพิสูจน์ยอดเงินฝากธนาคาร กองทุน  "'.$this->fund->get_one('name',$_POST['fund_id']).'" ปี '.$_POST['year'].' เดือน '.month($_POST['month']));
			}
			
			$balance = array(
				'id' => $_POST['id'],
				'fund_id' => $_POST['fund_id'],
				'agency_id' => $_POST['agency_id'],
				'user_id' => login_data('id'),
				'year_record' => ($_POST['year'] - 543),
				'month_record' => $_POST['month'],
				'balance_price' => str_ireplace(',', '', $_POST['balance_price'])
			);
			$balance_id = $this->balance->save($balance);

			// รายละเอียดเช็คที่สั่งจ่ายแล้วแต่ยังไม่นำไปขึ้นเงิน
			$this->db->execute('DELETE FROM BALANCE_CHECKS WHERE BALANCE_ID = '.$balance_id);
			foreach($_POST['price'] as $row => $price)
			{
				$data = array(
					'balance_id' => $balance_id,
					'date_record' => date_to_mysql($_POST['date_record'][$row]),
					'check_number' => $_POST['check_number'][$row],
					'receiver' => $_POST['receiver'][$row],
					'item' => $_POST['item'][$row],
					'price' => str_ireplace(',', '', $price),
					'ps' => $_POST['ps'][$row]
				);
				$this->balance_check->save($data);
			}
			
			// รายละเอียดเงินคงเหลือตามรายงานรับ - จ่ายเงินประจำเดือนประกอบด้วย
			$this->db->execute('DELETE FROM BALANCE_DETAILS WHERE BALANCE_ID = '.$balance_id);
			foreach($_POST['detail_title_id'] as $key => $value)
			{
				$detail = array(
					'balance_id' => $balance_id,
					'title_id' => $_POST['detail_title_id'][$key],
					'price' => $_POST['detail_price'][$key]
				);
				$this->balance_detail->save($detail);
			}
			
			$this->load->model('files/file_model');
			$this->load->model('files/file_item_model');
			Modules::run('files/save_file', $_POST['fund_id'], ($_POST['year'] - 543), $_POST['month'], $_POST['agency_id'], 'banking_balance');			

			$this->save_next_balance($_POST['fund_id'], $_POST['agency_id'], login_data('id'), $_POST['year'], $_POST['month'], $_POST['next_balance'], $_POST['register_id']);
			
			/*
			$this->load->model('voucher/status_model');
			$status = array(
				'status' => 1
			);
			$this->status_model->save($status);			
			*/
			
			// Update status
			//$this->db->execute('UPDATE STATUS SET STATUS.STATUS = 1 WHERE STATUS_YEAR = ? AND STATUS_MONTH = ?', array(($_POST['year']-543), $_POST['month']));

			set_notify('success', lang('save_data_complete'));
		}
		redirect('banking_balance/view/'.$_POST['fund_id'].'/'.$_POST['year'].'/'.$_POST['month']);
	}
	
	public function fix_title()
	{
		$sql = 'SELECT ID, COL_TITLE 
		FROM REGISTER_TITLES 
		ORDER BY ID ASC';
		return $this->db->getassoc($sql);
	}
	
	public function month_price($fund_id, $agency_id, $year, $month, $is_prev = FALSE)
	{
		$year -= 543;
		if($is_prev)
		{
			if($month == 1)
			{
				$year -= 1;
				$month = 12;
			}
			else
			{
				$month -= 1;
			}
		}
		
		$sql = "SELECT 
			REGISTER_TITLES.ID,
			P.VALUE
		FROM REGISTER_TITLES
		LEFT JOIN ( 
			SELECT REGISTERS.TITLE_ID, SUM(REGISTER_DETAILS.PRICE) AS VALUE
			FROM REGISTERS
			JOIN REGISTER_DETAILS ON REGISTER_DETAILS.REGISTER_ID = REGISTERS.ID
			WHERE REGISTERS.FUND_ID = $fund_id
			AND REGISTERS.AGENCY_ID = $agency_id
			AND YEAR(REGISTERS.DATE_RECORD) = $year 
			AND MONTH(REGISTERS.DATE_RECORD) = $month
			AND IS_PRICE = 1
			GROUP BY REGISTERS.TITLE_ID
		) AS P ON P.TITLE_ID = REGISTER_TITLES.ID
		ORDER BY REGISTER_TITLES.ID ASC";
		return $this->db->getassoc($sql);
	}
	
	/*
	 * ยอดคงเหลือตามรายงานรับ-จ่ายเงินประจำเดือน
	 */
	public function income_expenses_balance($fund_id, $agency_id, $year, $month)
	{
		$year -= 543;
		$sql = "SELECT SIDE, SUM(REGISTER_DETAILS.PRICE) AS TOTAL_PRICE
		FROM REGISTERS
		JOIN REGISTER_DETAILS ON REGISTER_DETAILS.REGISTER_ID = REGISTERS.ID
		WHERE REGISTERS.FUND_ID = $fund_id
		AND REGISTERS.AGENCY_ID = $agency_id
		AND YEAR(REGISTERS.DATE_RECORD) = $year
		AND MONTH(REGISTERS.DATE_RECORD) = $month
		AND IS_PRICE = 1
		GROUP BY SIDE";
		$priec = $this->db->getassoc($sql);
		return (!empty($priec)) ? ($priec[1] - $priec[2]) : NULL;
	}
	
	public function get_next_balance($fund_id, $agency_id, $year, $month)
	{
		$year -= 543;
		if($month == 12)
		{
			$year += 1;
			$month = 1;
		}
		else
		{
			$month += 1;
		}
		return $this->db->getone(
			'SELECT ID
			FROM REGISTERS
			WHERE FUND_ID = ?
			AND AGENCY_ID = ?
			AND YEAR(DATE_RECORD) = ?
			AND MONTH(DATE_RECORD) = ?
			AND TITLE_ID = 43'
		,array($fund_id, $agency_id, $year, $month));
	}
	
	public function save_next_balance($fund_id, $agency_id, $user_id, $year, $month, $price, $register_id = NULL)
	{	//$this->db->debug = true;
		
		
		$year -= 543;
		$month += 1;
		if($month == 13)
		{
			$year += 1;
			$month = 1;
		}
		
		$ids = $this->db->getarray('SELECT ID FROM REGISTERS WHERE FUND_ID = ? AND AGENCY_ID = ? AND YEAR(DATE_RECORD) = ? AND MONTH(DATE_RECORD) = ?',
		array($fund_id, $agency_id, $year, $month));
		
		$this->db->execute('DELETE FROM REGISTERS WHERE ID IN ('.implode(',', $ids).')');
		$this->db->execute('DELETE FROM REGISTER_DETAILS WHERE REGISTER_ID IN ('.implode(',', $ids).')');
		
		// บันทึกลงทะเบียนคุมเงิน
		$data = array(
			'id' => $register_id,
			'fund_id' => $fund_id,
			'agency_id' => $agency_id,
			'user_id' => $user_id,
			'date_record' => $year.'-'.$month.'-1',
			'title_id' => 43,
			'side' => 1
		);
		$regiser_id = $this->register->save($data);
			
		// บันทึกรายละเอียดทะเบียนคุมเงิน
		$this->db->execute('DELETE FROM REGISTER_DETAILS WHERE REGISTER_ID = '.$regiser_id);
		$this->register_detail->save(array(
			'register_id' => $regiser_id,
			'col' => 58,
			'price' => str_replace(',', '', $price),
			'is_price' => 1
		));
	}
}
?>